﻿using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using ARMS.BaseClasses;
using ARMS.Models;

namespace ARMS.Models
{
    public class WorkOrderModel : ARMS.BaseClasses.BaseModel
    {
        #region WorkOrder Class
        public class WorkOrder
        {
            public WorkOrder() { }
            public WorkOrder(int WorkOrderID, int BookingID, int CustomerID, int WorkerID, String WorkNote, String Status)
            {
                this.WorkOrderID = WorkOrderID;
                this.BookingID = BookingID;
                this.CustomerID = CustomerID;
                this.WorkerID = WorkerID;
                this.WorkNote = WorkNote;
                this.Status = Status;
            }

            public int WorkOrderID { get; set; }
            public int BookingID { get; set; }
            public int CustomerID { get; set; }
            public int WorkerID { get; set; }
            public String WorkNote { get; set; }
            public String Status { get; set; }
        }
        #endregion

        //string dbConn = "Provider=SQLNCLI11; Data Source=(LocalDB)\\v11.0;Initial Catalog=arms;Integrated Security=SSPI";
        //string dbConn = "Data Source=(LocalDB)\\v11.0;Initial Catalog=arms;Integrated Security=SSPI";
        //Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|arms.mdf;Integrated Security=True
        
        const string SCHEDULED = "scheduled";
        const string WORKING = "working";
        const string COMPLETED = "completed";
        const string CANCELED = "canceled";

        /* DB struct
        [id] [int] IDENTITY(1,1) NOT NULL,
        [bookingid] [int] NOT NULL,
        [customerid] [int] NOT NULL,
        [workerid] [int] NOT NULL,
        [worknote] [nvarchar](MAX) NOT NULL,
        [status] [nvarchar](20) NOT NULL,
         */

        #region Create Work Order

        /// <summary>
        /// A database connection that create a new work order record
        /// </summary>
        /// <param name="BookingID">An integer represeting a BookingID</param>
        /// <param name="CustomerID">An integer represeting a CustomerID</param>
        /// <param name="WorkerID">An integer represeting a WorkerID</param>
        /// <param name="Status">A string representing a Status</param>
        /// <param name="Note">A string representing a Note</param>
        public int createWorkOrder(int BookingID, int CustomerID, int WorkerID, string Status, string WorkNote)
        {
            //Connect to Database
            //OleDbConnection connection = new OleDbConnection(dbConn);
            //OleDbConnection WOconn = new OleDbConnection(dbConn);

            //WOconn.Open();

            int affectedRows = 0;

            if (String.IsNullOrEmpty(Status) || String.IsNullOrEmpty(WorkNote))
            {
                throw new NullReferenceException();
            }
            else
            {
                if (Status.ToLower() == SCHEDULED || Status.ToLower() == WORKING || Status.ToLower() == CANCELED || Status.ToLower() == COMPLETED)
                {
                    if (BookingID <= 0 || CustomerID <= 0 || WorkerID <= 0)
                    {
                        throw new ArgumentException();
                    }
                    else
                    {
                        try
                        {
                            //create new work order
                            string sqlQuery = "INSERT INTO workorder (bookingid, customerid, workerid, worknote, status) VALUES(" + BookingID + ", " + CustomerID + ", " + WorkerID + ", '" + Status + "', '" + WorkNote + "')";
                            //OleDbCommand command = new OleDbCommand(sqlQuery, connection);
                            //OleDbCommand command = new OleDbCommand(sqlQuery, WOconn);
                            //affectedRows = command.ExecuteNonQuery();

                            affectedRows = Execute(sqlQuery);
                        }
                        catch (Exception e)
                        {
                            throw new HttpException(e.ToString());
                        }
                    }
                }
                else
                {
                    throw new ArgumentException();
                }
            }

            //WOconn.Close();
            return affectedRows;
        }

        #endregion

        #region Update Work Order

        /// <summary>
        /// A database connection to update a work order record
        /// </summary>
        /// <param name="WorkerID">A integer representing a WorkerID</param>
        /// <param name="Status">A string representing a Status</param>
        /// <param name="Note">A string representing a Note</param>
        /// <param name="WorkOrderID">A integer representing a WorkOrderID</param>
        public int updateWorkOrder(int WorkerID, string Status, string WorkNote, int WorkOrderID)
        {
            int affectedRows = 0;

            //Connect to Database
            //OleDbConnection WOconn = new OleDbConnection(dbConn);
            //WOconn.Open();

            if (String.IsNullOrEmpty(Status) || String.IsNullOrEmpty(WorkNote))
            {
                throw new NullReferenceException();
            }
            else
            {
                if (Status.ToLower() == SCHEDULED || Status.ToLower() == WORKING || Status.ToLower() == CANCELED || Status.ToLower() == COMPLETED)
                {
                    if (WorkerID <= 0 || WorkOrderID <= 0)
                    {
                        throw new ArgumentException();
                    }
                    else
                    {
                        try
                        {
                            //create sql for update work order
                            string sqlQuery = "UPDATE workorder SET workerid=" + WorkerID + ", status='" + Status + "', worknote='" + WorkNote + "' WHERE ((id=" + WorkOrderID + "))";
                            //OleDbCommand command = new OleDbCommand(sqlQuery, WOconn);
                            //affectedRows = command.ExecuteNonQuery();
                            affectedRows = Execute(sqlQuery);
                        }
                        catch (Exception e)
                        {
                            throw new HttpException(e.ToString());
                        }
                    }
                }
                else
                {
                    throw new ArgumentException();
                }
            }

            //WOconn.Close();
            return affectedRows;
        }

        #endregion

        #region Delete Work Order

        public int deleteWorkOrder(int WorkOrderID)
        {
            int affectedRows = 0;
            DataSet results = new DataSet();

            //Connect to Database
            //OleDbConnection WOconn = new OleDbConnection(dbConn);
            //WOconn.Open();


            if (String.IsNullOrEmpty(Convert.ToString(WorkOrderID)))
            {
                throw new NullReferenceException();
            }
            else
            {
                if (Convert.ToInt32(WorkOrderID) <= 0)
                {
                    throw new ArgumentException();
                }
                else
                {
                    try
                    {
                        //create sql for delete work order
                        string sqlQuery = "DELETE FROM workorder WHERE ((id= " + WorkOrderID + "))";
                        //OleDbCommand command = new OleDbCommand(sqlQuery, WOconn);
                        //affectedRows = command.ExecuteNonQuery();
                        affectedRows = Execute(sqlQuery);
                    }
                    catch (Exception e)
                    {
                        throw new HttpException(e.ToString());
                    }
                }
            }

            //WOconn.Close();
            return affectedRows;
        }

        #endregion

        #region Return Work Order

        public DataSet returnWorkOrder(int WorkOrderID)
        {
            DataSet results = new DataSet();
            //DataTable tWorkOrder = new DataTable();
            //DataTable tPartOrder = new DataTable();
            //DataTable tWorker = new DataTable();

            //Connect to Database
            //OleDbConnection WOconn = new OleDbConnection(dbConn);
            //WOconn.Open();

            if (String.IsNullOrEmpty(Convert.ToString(WorkOrderID)))
            {
                throw new NullReferenceException();
            }
            else
            {
                if (Convert.ToInt32(WorkOrderID) <= 0)
                {
                    throw new ArgumentException();
                }
                else
                {
                    try
                    {
                        //connect to database
                        //string sqlQuery = "SELECT * FROM workorder WHERE (id = " + WorkOrderID + ")";
                        string sqlQuery = "SELECT workorder.id, workorder.bookingid, workorder.customerid, customer.firstname, customer.lastname, " +
                            "workorder.workerid, workorder.status, workorder.worknote, booking.note " +
                            "FROM (customer RIGHT JOIN booking ON customer.id = booking.customerid ) " +
                            "RIGHT JOIN workorder ON booking.id = workorder.bookingid " +
                            "WHERE ((workorder.id = " + WorkOrderID + "))";
                        //OleDbCommand cmd = new OleDbCommand(sqlQuery, WOconn);

                        string sqlQuery2 = "Select * FROM partorder WHERE ((id= " + WorkOrderID + "))";
                        //OleDbCommand cmd2 = new OleDbCommand(sqlQuery2, WOconn);

                        string sqlQuery3 = "Select * FROM workersview";
                        //OleDbCommand cmd3 = new OleDbCommand(sqlQuery3, WOconn);

                        //OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                        //adapter.Fill(tWorkOrder);
                        //OleDbDataAdapter adapter2 = new OleDbDataAdapter(cmd2);
                        //adapter2.Fill(tPartOrder);
                        //OleDbDataAdapter adapter3 = new OleDbDataAdapter(cmd3);
                        //adapter3.Fill(tWorker);

                        results.Tables.Add(OpenTable(sqlQuery));
                        results.Tables.Add(OpenTable(sqlQuery2));
                        results.Tables.Add(OpenTable(sqlQuery3));

                        //results.Tables.Add(tWorkOrder);
                        //results.Tables.Add(tPartOrder);
                        //results.Tables.Add(tWorker);

                        //adapter3.Dispose();
                        //adapter2.Dispose();
                        //adapter.Dispose();
                    }
                    catch (Exception e)
                    {
                        throw new HttpException(e.ToString());
                    }
                }
            }

            //WOconn.Close();
            return results;
        }

        #endregion

        #region List Work Order

        /// <summary>
        /// Retrieves all records in the Work Order table
        /// </summary>
        /// <returns>DataTable containing all Work Orders</returns>
        public DataTable listWorkOrder()
        {
            DataTable results = new DataTable();
            //Connect to Database
            //OleDbConnection WOconn = new OleDbConnection(dbConn);
            //WOconn.Open();

            try
            {
                //connect to database
                string sqlQuery = "Select * FROM workorder";
                //OleDbCommand cmd = new OleDbCommand(sqlQuery, WOconn);
                //OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);

                //adapter.Fill(results);
                //adapter.Dispose();
                results = OpenTable(sqlQuery);
            }
            catch (Exception e)
            {
                throw new HttpException(e.ToString());
            }

            //WOconn.Close();
            return results;
        }

        #endregion
    }
}